package com.cal.utms.dao.dealing;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;

import com.cal.utms.entity.Instrument;
import com.cal.utms.entity.InstrumentCouponDate;
import com.cal.utms.entity.InstrumentFloatingRate;
import com.cal.utms.util.HibernateUtil;

public class InstrumentDao {

	public Object add(Instrument instrument) {
		Object obj;
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		System.out.println("Saving new instrument");
		try {
			tx = session.beginTransaction();
			obj = session.save(instrument);
			tx.commit();
			System.out.println("Instrument Added: " + instrument.getInstrumentCode());
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			System.out.println("Exception in save Instrument " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public void update(Instrument instrument) {
		System.out.println("Updating the instrument");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(instrument);
			tx.commit();
			System.out.println("Instrument updated");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Instrument. ID: " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public void setFree(Instrument instrument) {
		System.out.println("Updating the instrument");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();

			if (instrument.getInstrumentCouponDates() != null)
				for (InstrumentCouponDate icd : instrument.getInstrumentCouponDates())
					session.delete(icd);

			if (instrument.getInstrumentFloatingRates() != null)
				for (InstrumentFloatingRate ifr : instrument.getInstrumentFloatingRates())
					session.delete(ifr);
			instrument.setStatus("Free");
			session.update(instrument);
			tx.commit();
			System.out.println("Instrument updated");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Instrument. ID: " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public void delete(Instrument instrument) {
		System.out.println("deleting the instrument");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			if (instrument.getInstrumentCouponDates() != null)
				for (InstrumentCouponDate icd : instrument.getInstrumentCouponDates())
					session.delete(icd);

			if (instrument.getInstrumentFloatingRates() != null)
				for (InstrumentFloatingRate ifr : instrument.getInstrumentFloatingRates())
					session.delete(ifr);

			session.delete(instrument);
			tx.commit();
			System.out.println("Instrument deleted");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in deleting the Instrument. ID: " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public Instrument getById(Integer id) {
		Session session = HibernateUtil.getSession().openSession();
		Instrument instrument = null;
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			instrument = (Instrument) session.get(Instrument.class, id);
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Instrument " + e.getMessage());
		} finally {
			session.close();
		}
		return instrument;
	}

	public Instrument getByCode(String code) {
		Session session = HibernateUtil.getSession().openSession();
		Instrument instrument = null;
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("select c from Instrument c where c.instrumentCode = :code");
			query.setParameter("code", code);
			if (query.list().size() > 0) {
				instrument = (Instrument) query.list().get(0);
			} else {
				instrument = null;
			}
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Instrument " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return instrument;
	}

	// Query query =
	// session.createQuery("select c from CustodianContactPerson c where c.instrument.id = :instrumentId");

	@SuppressWarnings("unchecked")
	public List<Instrument> getInstrumentCodes() {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("instrumentType"), "instrumentType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		projectionList.add(Projections.property("issueDate"), "issueDate");
		cr.setProjection(projectionList);
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getSalableInsCodes(int fundId) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		cr.createAlias("instrumentType", "type");
		cr.createAlias("lastDeal", "l");
		cr.createAlias("l.fund", "lf");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("instrumentType"), "instrumentType");
		projectionList.add(Projections.property("instrumentValue"), "instrumentValue");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		// projectionList.add(Projections.property("lastDeal"), "lastDeal");
		cr.setProjection(projectionList);
		// cr.add(Restrictions.eq("type.id", insTypeId));
		cr.add(Restrictions.eq("this.status", "IN"));
		cr.add(Restrictions.eq("lf.id", fundId));
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getInInsCodesForFund(int fundId) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		cr.createAlias("instrumentType", "type");
		cr.createAlias("lastDeal", "l");
		cr.createAlias("l.fund", "lf");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("instrumentType"), "instrumentType");
		projectionList.add(Projections.property("issueDate"), "issueDate");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		// projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		// projectionList.add(Projections.property("lastDeal"), "lastDeal");
		cr.setProjection(projectionList);
		// cr.add(Restrictions.eq("type.id", insTypeId));
		cr.add(Restrictions.eq("this.status", "IN"));
		cr.add(Restrictions.eq("lf.id", fundId));
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getFreeListed() {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		cr.createAlias("instrumentType", "type");
		// cr.createAlias("lastDeal", "l");
		// cr.createAlias("l.fund", "lf");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("instrumentType"), "instrumentType");
		projectionList.add(Projections.property("instrumentValue"), "instrumentValue");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		// projectionList.add(Projections.property("lastDeal"), "lastDeal");
		cr.setProjection(projectionList);
		// cr.add(Restrictions.eq("type.id", insTypeId));
		cr.add(Restrictions.eq("this.status", "Free"));
		cr.add(Restrictions.eq("type.listed", (byte) 1));
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getSalableInsCodes(int fundId, int insTypeId) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		cr.createAlias("instrumentType", "type");
		cr.createAlias("lastDeal", "l");
		cr.createAlias("l.fund", "lf");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("type.id", insTypeId));
		cr.add(Restrictions.eq("this.status", "IN"));
		cr.add(Restrictions.eq("lf.id", fundId));
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getNames(String query) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Instrument.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("instrumentCode"), "instrumentCode");
		projectionList.add(Projections.property("instrumentType"), "instrumentType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		cr.setProjection(projectionList);
		cr.setResultTransformer(Transformers.aliasToBean(Instrument.class));
		List<Instrument> list = cr.list();
		session.close();
		List<Instrument> res = new ArrayList<Instrument>();
		for (Instrument instrument : list) {
			if (instrument.getStatus().equals("Free") && instrument.getInstrumentCode().toUpperCase().startsWith(query.toUpperCase())) {
				res.add(instrument);
			}
		}
		return res;
	}

	public static void main(String[] args) {
		Session session = HibernateUtil.getSession().openSession();

		/*
		 * Criteria cr = session.createCriteria(Instrument.class)
		 * .setProjection(Projections.projectionList()
		 * .add(Projections.property("id"), "id")
		 * .add(Projections.property("instrumentCode"), "instrumentCode")
		 * .add(Projections.property("instrumentName"), "instrumentName"))
		 * .setResultTransformer(Transformers.aliasToBean(Instrument.class));
		 * 
		 * @SuppressWarnings("unchecked") List<Instrument> list = cr.list();
		 * for(Instrument instrument:list){
		 * System.out.println(instrument.getId()
		 * +" : "+instrument.getInstrumentCode
		 * ()+" : "+instrument.getInstrumentName()); }
		 */

		InstrumentDao nfd = new InstrumentDao();
		// Instrument instrument = nfd.getByCode("CODE4");
		for (Instrument instrument : nfd.getSalableInsCodes(16)) {
			System.out.println(instrument.getId() + ":" + instrument.getStatus());
			/*
			 * try{ System.out.println("ld:"+instrument.getLastDeal());
			 * }catch(Exception e){ System.out.println("exxx"); }
			 */
		}

		session.close();
	}

	public boolean updateCouponDates(Instrument instrument) {
		boolean status = false;
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		System.out.println("Updating Coupon Dates for " + instrument.getInstrumentCode());
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("delete InstrumentCouponDate where instrument = :instrument");
			query.setParameter("instrument", instrument);
			query.executeUpdate();

			if (instrument.getInstrumentCouponDates() != null && instrument.getInstrumentCouponDates().size() > 0)
				for (InstrumentCouponDate icd : instrument.getInstrumentCouponDates()) {
					session.save(icd);
				}
			tx.commit();
			System.out.println("Coupon Dates Updated for: " + instrument.getInstrumentCode());
			status = true;
		} catch (Exception ex) {
			tx.rollback();
			status = false;
			System.out.println("Exception in Updating Coupon Dates: " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return status;
	}

	public boolean updateFloatingRates(Instrument instrument) {
		boolean status = false;
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		System.out.println("Updating Floating Rates for " + instrument.getInstrumentCode());
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("delete InstrumentFloatingRate where instrument = :instrument");
			query.setParameter("instrument", instrument);
			query.executeUpdate();

			if (instrument.getInstrumentFloatingRates() != null && instrument.getInstrumentFloatingRates().size() > 0)
				for (InstrumentFloatingRate ifr : instrument.getInstrumentFloatingRates()) {
					session.save(ifr);
				}
			tx.commit();
			System.out.println("Floating Rates Updated for: " + instrument.getInstrumentCode());
			status = true;
		} catch (Exception ex) {
			tx.rollback();
			status = false;
			System.out.println("Exception in Updating Coupon Dates: " + instrument.getInstrumentCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return status;
	}

	@SuppressWarnings("unchecked")
	public List<Instrument> getInstrumentByType(int typeid) {
		Session session = HibernateUtil.getSession().openSession();
		Transaction transaction = null;
		List<Instrument> instruments = null;
		try {
			transaction = session.beginTransaction();
			instruments = session.createQuery("From Instrument I where I.instrumentType.id=:type").setParameter("type", typeid).list();
		} catch (Exception e) {
			if (transaction != null)
				transaction.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

		return instruments;
	}
}
